﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class Resource
    {
        public Resource()
        {

        }

        public static Guid CreateResource(DTO.Resource resource, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateResource]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter InstanceIDParam = new SqlParameter("@InstanceID", SqlDbType.UniqueIdentifier);
                    SqlParameter IDLParam = new SqlParameter("@IDL", SqlDbType.NVarChar);
                    SqlParameter IDLTypeParam = new SqlParameter("@IDLType", SqlDbType.NChar, 100);
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter MonikerParam = new SqlParameter("@Moniker", SqlDbType.Xml);
                    SqlParameter RequestEntityParam = new SqlParameter("@RequestEntity", SqlDbType.UniqueIdentifier);
                    SqlParameter ResponseEntityParam = new SqlParameter("@ResponseEntity", SqlDbType.UniqueIdentifier);
                    SqlParameter URIParam = new SqlParameter("@URI", SqlDbType.NVarChar);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    SqlParameter ShareableProxyParam = new SqlParameter("@ShareableProxy", SqlDbType.Bit);
                    SqlParameter AvgResponseDaysParam = new SqlParameter("@AvgResponseDays", SqlDbType.TinyInt);
                    SqlParameter AvgResponseTimeMSParam = new SqlParameter("@AvgResponseTimeMS", SqlDbType.Int);
                    SqlParameter MaxResponseDaysParam = new SqlParameter("@MaxResponseDays", SqlDbType.TinyInt);
                    SqlParameter MaxResponseTimeMSParam = new SqlParameter("@MaxResponseTimeMS", SqlDbType.Int);
                    SqlParameter AvailabilityParam = new SqlParameter("@Availability", SqlDbType.Decimal);
                    SqlParameter ConcurrentUsersParam = new SqlParameter("@ConcurrentUsers", SqlDbType.Int);
                    SqlParameter MinExpirationDateParam = new SqlParameter("@MinExpirationDate", SqlDbType.DateTime);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = resource.Name;
                    DescriptionParam.Value = resource.Description;
                    if (resource.InstanceID != null && resource.InstanceID != Guid.Empty)
                        InstanceIDParam.Value = resource.InstanceID;
                    IDLParam.Value = resource.IDL;
                    IDLTypeParam.Value = resource.IDLType;
                    BindingIDParam.Value = resource.Binding.ID;
                    if (resource.Moniker != null)
                    {
                        MonikerParam.Value = resource.Moniker.OuterXml;
                    }                    
                    if (resource.RequestSchemaObject != null)
                    {
                        RequestEntityParam.Value = resource.RequestSchemaObject.ID;
                    }
                    else
                    {
                        RequestEntityParam.Value = DBNull.Value;
                    }

                    if (resource.ResponseSchemaObject != null)
                    {
                        ResponseEntityParam.Value = resource.ResponseSchemaObject.ID;
                    }
                    else
                    {
                        ResponseEntityParam.Value = DBNull.Value;
                    }

                    URIParam.Value = resource.URI;
                    ShareableProxyParam.Value = resource.ShareableProxy;
                    if (resource.Policy != null)
                    {
                        PolicyIDParam.Value = resource.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }
                    if (resource.AvgResponseDays != 0)
                    {
                        AvgResponseDaysParam.Value = resource.AvgResponseDays;
                    }
                    else
                    {
                        AvgResponseDaysParam.Value = DBNull.Value;
                    }

                    if (resource.AvgResponseTimeMS != 0)
                    {
                        AvgResponseTimeMSParam.Value = resource.AvgResponseTimeMS;
                    }
                    else
                    {
                        AvgResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (resource.MaxResponseDays != 0)
                    {
                        MaxResponseDaysParam.Value = resource.MaxResponseDays;
                    }
                    else
                    {
                        MaxResponseDaysParam.Value = DBNull.Value;
                    }

                    if (resource.MaxResponseTimeMS != 0)
                    {
                        MaxResponseTimeMSParam.Value = resource.MaxResponseTimeMS;
                    }
                    else
                    {
                        MaxResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (resource.Availability != 0)
                    {
                        AvailabilityParam.Value = resource.Availability;
                    }
                    else
                    {
                        AvailabilityParam.Value = DBNull.Value;
                    }

                    if (resource.ConcurrentUsers != 0)
                    {
                        ConcurrentUsersParam.Value = resource.ConcurrentUsers;
                    }
                    else
                    {
                        ConcurrentUsersParam.Value = DBNull.Value;
                    }

                    if (resource.MinExpirationDate != null && DateTime.Compare(resource.MinExpirationDate, DateTime.Now) > 0)
                    {
                        MinExpirationDateParam.Value = resource.MinExpirationDate;
                    }
                    else
                    {
                        MinExpirationDateParam.Value = DBNull.Value;
                    }


                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(InstanceIDParam);
                    cmd.Parameters.Add(IDLParam);
                    cmd.Parameters.Add(IDLTypeParam);
                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(MonikerParam);
                    cmd.Parameters.Add(RequestEntityParam);
                    cmd.Parameters.Add(ResponseEntityParam);
                    cmd.Parameters.Add(URIParam);
                    cmd.Parameters.Add(PolicyIDParam);
                    cmd.Parameters.Add(ShareableProxyParam);
                    cmd.Parameters.Add(AvgResponseDaysParam);
                    cmd.Parameters.Add(AvgResponseTimeMSParam);
                    cmd.Parameters.Add(MaxResponseDaysParam);
                    cmd.Parameters.Add(MaxResponseTimeMSParam);
                    cmd.Parameters.Add(AvailabilityParam);
                    cmd.Parameters.Add(ConcurrentUsersParam);
                    cmd.Parameters.Add(MinExpirationDateParam);


                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        public static DTO.Resource GetResource(Guid ID, bool loadPolicyTree, DTO.Policy opVersionPolicy, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.Resource resource = new DTO.Resource();
            const string sprocname = "[ServiceEngine].[GetResource]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            resource.ID = reader.GetGuid(0);
                            resource.Name = reader.GetString(1);
                            if (!reader.IsDBNull(2))
                            {
                                resource.InstanceID = reader.GetGuid(2);
                            }
                            if (reader.IsDBNull(3))
                            {
                                resource.IDL = null;
                            }
                            else
                            {
                                //resource.IDL = reader.GetString(3);
                                resource.IDL = null;
                            }
                            if (reader.IsDBNull(4))
                            {
                                resource.IDLType = null;
                            }
                            else
                            {
                                resource.IDLType = reader.GetString(4);
                            }

                            resource.Binding = reader.IsDBNull(5) ? null : CatalogSvcHelper.AssembleBindingFromBindingElements(Binding.GetMSEBinding(reader.GetGuid(5), txn, conn));

                            if (reader.IsDBNull(6))
                            {
                                resource.Moniker = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(6).CreateReader());
                                resource.Moniker = xdoc.DocumentElement;
                            }
                            if (reader.IsDBNull(7))
                            {
                                resource.Description = null;
                            }
                            else
                            {
                                resource.Description = reader.GetString(7);
                            }

                            if (!reader.IsDBNull(8))
                            {
                                resource.RequestSchemaObject = SchemaObject.GetSchemaObject(reader.GetGuid(8),txn,conn);
                            }

                            if (!reader.IsDBNull(9))
                            {
                                resource.ResponseSchemaObject = SchemaObject.GetSchemaObject(reader.GetGuid(9), txn, conn);
                            }

                            if (reader.IsDBNull(10))
                            {
                                resource.URI = null;
                            }
                            else
                            {
                                resource.URI = reader.GetString(10);
                            }

                            //TODO may need to get policies from systemInstance and mseSystem and create a composite policy
                            if (loadPolicyTree)
                            {
                                Guid policyID = Guid.Empty;
                                if (!reader.IsDBNull(11)) policyID = reader.GetGuid(11);
                                resource.Policy = CatalogSvcHelper.AssemblePolicyFromPolicyAssertion(policyID, resource, opVersionPolicy, txn, conn);
                            }
                            else
                            {
                                resource.Policy = reader.IsDBNull(11) ? null : CatalogSvcHelper.AssemblePolicyFromPolicyAssertion(reader.GetGuid(11), txn, conn);
                            }

                            resource.ShareableProxy = reader.GetBoolean(12);

                            if (reader.IsDBNull(13))
                            {
                                resource.AvgResponseDays = 0;
                            }
                            else
                            {
                                resource.AvgResponseDays = reader.GetByte(13);
                            }
                            if (reader.IsDBNull(14))
                            {
                                resource.AvgResponseTimeMS = 0;
                            }
                            else
                            {
                                resource.AvgResponseTimeMS = reader.GetInt32(14);
                            }
                            if (reader.IsDBNull(15))
                            {
                                resource.MaxResponseDays = 0;
                            }
                            else
                            {
                                resource.MaxResponseDays = reader.GetByte(15);
                            }
                            if (reader.IsDBNull(16))
                            {
                                resource.MaxResponseTimeMS = 0;
                            }
                            else
                            {
                                resource.MaxResponseTimeMS = reader.GetInt32(16);
                            }
                            
                            if (reader.IsDBNull(17))
                            {
                                resource.ConcurrentUsers = 0;
                            }
                            else
                            {
                                resource.ConcurrentUsers = reader.GetInt32(17);
                            }
                            if (reader.IsDBNull(18))
                            {
                                resource.Availability = 0;
                            }
                            else
                            {
                                resource.Availability = reader.GetDecimal(18);
                            }
                            if (!reader.IsDBNull(19))                            
                            {
                                resource.MinExpirationDate = reader.GetDateTime(19);
                            }

                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return resource;

        }

        public static void DeleteResource(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteResource]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static DTO.Resource UpdateResource(DTO.Resource resource, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateResource]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter InstanceIDParam = new SqlParameter("@InstanceID", SqlDbType.UniqueIdentifier);
                    SqlParameter IDLParam = new SqlParameter("@IDL", SqlDbType.NVarChar);
                    SqlParameter IDLTypeParam = new SqlParameter("@IDLType", SqlDbType.NChar, 100);
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter MonikerParam = new SqlParameter("@Moniker", SqlDbType.Xml);
                    SqlParameter RequestEntityParam = new SqlParameter("@RequestEntity", SqlDbType.UniqueIdentifier);
                    SqlParameter ResponseEntityParam = new SqlParameter("@ResponseEntity", SqlDbType.UniqueIdentifier);
                    SqlParameter URIParam = new SqlParameter("@URI", SqlDbType.NVarChar);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    SqlParameter ShareableProxyParam = new SqlParameter("@ShareableProxy", SqlDbType.Bit);
                    SqlParameter AvgResponseDaysParam = new SqlParameter("@AvgResponseDays", SqlDbType.TinyInt);
                    SqlParameter AvgResponseTimeMSParam = new SqlParameter("@AvgResponseTimeMS", SqlDbType.Int);
                    SqlParameter MaxResponseDaysParam = new SqlParameter("@MaxResponseDays", SqlDbType.TinyInt);
                    SqlParameter MaxResponseTimeMSParam = new SqlParameter("@MaxResponseTimeMS", SqlDbType.Int);
                    SqlParameter AvailabilityParam = new SqlParameter("@Availability", SqlDbType.Decimal);
                    SqlParameter ConcurrentUsersParam = new SqlParameter("@ConcurrentUsers", SqlDbType.Int);
                    SqlParameter MinExpirationDateParam = new SqlParameter("@MinExpirationDate", SqlDbType.DateTime);


                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = resource.ID;
                    NameParam.Value = resource.Name;
                    if (resource.InstanceID != null && resource.InstanceID != Guid.Empty)
                        InstanceIDParam.Value = resource.InstanceID;
                    IDLParam.Value = resource.IDL;
                    IDLTypeParam.Value = resource.IDLType;
                    DescriptionParam.Value = resource.Description;
                    BindingIDParam.Value = resource.Binding.ID;
                    if (resource.Moniker != null)
                    {
                        MonikerParam.Value = resource.Moniker.OuterXml;
                    }                    
                    if (resource.RequestSchemaObject != null)
                    {
                        RequestEntityParam.Value = resource.RequestSchemaObject.ID;
                    }
                    else
                    {
                        RequestEntityParam.Value = DBNull.Value;
                    }

                    if (resource.ResponseSchemaObject != null)
                    {
                        ResponseEntityParam.Value = resource.ResponseSchemaObject.ID;
                    }
                    else
                    {
                        ResponseEntityParam.Value = DBNull.Value;
                    }
                    URIParam.Value = resource.URI;
                    ShareableProxyParam.Value = resource.ShareableProxy;
                    if (resource.Policy != null)
                    {
                        PolicyIDParam.Value = resource.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }
                    if (resource.AvgResponseDays != 0)
                    {
                        AvgResponseDaysParam.Value = resource.AvgResponseDays;
                    }
                    else
                    {
                        AvgResponseDaysParam.Value = DBNull.Value;
                    }

                    if (resource.AvgResponseTimeMS != 0)
                    {
                        AvgResponseTimeMSParam.Value = resource.AvgResponseTimeMS;
                    }
                    else
                    {
                        AvgResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (resource.MaxResponseDays != 0)
                    {
                        MaxResponseDaysParam.Value = resource.MaxResponseDays;
                    }
                    else
                    {
                        MaxResponseDaysParam.Value = DBNull.Value;
                    }

                    if (resource.MaxResponseTimeMS != 0)
                    {
                        MaxResponseTimeMSParam.Value = resource.MaxResponseTimeMS;
                    }
                    else
                    {
                        MaxResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (resource.Availability != 0)
                    {
                        AvailabilityParam.Value = resource.Availability;
                    }
                    else
                    {
                        AvailabilityParam.Value = DBNull.Value;
                    }

                    if (resource.ConcurrentUsers != 0)
                    {
                        ConcurrentUsersParam.Value = resource.ConcurrentUsers;
                    }
                    else
                    {
                        ConcurrentUsersParam.Value = DBNull.Value;
                    }

                    if (resource.MinExpirationDate != null && DateTime.Compare(resource.MinExpirationDate,DateTime.Now) > 0)
                    {
                        MinExpirationDateParam.Value = resource.MinExpirationDate;
                    }
                    else
                    {
                        MinExpirationDateParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(InstanceIDParam);
                    cmd.Parameters.Add(IDLParam);
                    cmd.Parameters.Add(IDLTypeParam);
                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(MonikerParam);
                    cmd.Parameters.Add(RequestEntityParam);
                    cmd.Parameters.Add(ResponseEntityParam);
                    cmd.Parameters.Add(URIParam);
                    cmd.Parameters.Add(PolicyIDParam);
                    cmd.Parameters.Add(ShareableProxyParam);
                    cmd.Parameters.Add(AvgResponseDaysParam);
                    cmd.Parameters.Add(AvgResponseTimeMSParam);
                    cmd.Parameters.Add(MaxResponseDaysParam);
                    cmd.Parameters.Add(MaxResponseTimeMSParam);
                    cmd.Parameters.Add(AvailabilityParam);
                    cmd.Parameters.Add(ConcurrentUsersParam);
                    cmd.Parameters.Add(MinExpirationDateParam);

                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return resource;
        }

        public static DTO.ResourceList ListResources(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.ResourceList list = new DTO.ResourceList();
            const string sprocname = "[ServiceEngine].[ListResources]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Resources = new List<DTO.ResourceListElement>();
                            while (reader.Read())
                            {
                                DTO.ResourceListElement element = new DTO.ResourceListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Resources.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.ResourceList ListResourcesForSystemInstance(Guid InstanceID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.ResourceList list = new DTO.ResourceList();
            const string sprocname = "[ServiceEngine].[ListResourcesForSystemInstance]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@InstanceID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = InstanceID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Resources = new List<DTO.ResourceListElement>();
                            while (reader.Read())
                            {
                                DTO.ResourceListElement element = new DTO.ResourceListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Resources.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.ResourceListElement> ListResourcesByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.ResourceListElement> list = new List<DTO.ResourceListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.ResourceListElement>();
                            while (reader.Read())
                            {
                                DTO.ResourceListElement element = new DTO.ResourceListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.ResourceList ListResourcesForBindingID(Guid bindingID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.ResourceList list = new DTO.ResourceList();
            const string sprocname = "[ServiceEngine].[ListResourcesForBindingID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = bindingID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Resources = new List<DTO.ResourceListElement>();
                            while (reader.Read())
                            {
                                DTO.ResourceListElement element = new DTO.ResourceListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Resources.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }

            return list;

        }
        public static List<DTO.Entity> ListResourcesForSchemaObject(Guid schemaObjectID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.Entity> resourceList = new List<DTO.Entity>();
            const string sprocname = "[ServiceEngine].[ListResourcesForSchemaObject]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaObjectID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = schemaObjectID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                DTO.Entity element = new DTO.Entity();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                element.EntityClass = DTO.EntityClass.Resource;
                                resourceList.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }

            return resourceList;

        }

        public static Guid GetIDForResource(string name, string scheme, string uri, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[GetIDForResource]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter SchemeParam = new SqlParameter("@Scheme", SqlDbType.NVarChar, 64);
                    SqlParameter UriParam = new SqlParameter("@URI", SqlDbType.NVarChar);
                    SqlParameter IDParam = new SqlParameter("@ResourceID", SqlDbType.UniqueIdentifier);
                    IDParam.Direction = ParameterDirection.Output;


                    cmd.CommandType = CommandType.StoredProcedure;

                    SchemeParam.Value = scheme;
                    NameParam.Value = name;

                    if (string.IsNullOrEmpty(uri) == false && uri.Length > 0)
                        UriParam.Value = uri;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(SchemeParam);                    
                    cmd.Parameters.Add(UriParam);

                    cmd.ExecuteNonQuery();
                    if (IDParam.Value != DBNull.Value)
                        ID = (Guid)IDParam.Value;
                    else
                        ID = Guid.Empty;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

    }
}
